<?php
/*
 * Request toilets.
*/

$params = array("id","lat","lng","name","rating","votes","address","city","state","zip");
$table = "toilets";
$response = array();

// base query
$query = "SELECT * FROM `$table` ";
$and = false; // append "AND" to query when compound predicate
$or = false; // append "OR" to query under amenities subquery

// if looking for toilets within a certain distance
if (isset($_POST["distance"]) && isset($_POST["lat_orig"]) && isset($_POST["lng_orig"])) {
	$query = $query . ",(SELECT id, ( 6371 * acos( cos( radians(" . $_POST["lat_orig"] . ") )
					 * cos( radians( lat ) ) * cos( radians( lng ) - radians(" . $_POST["lng_orig"] . ") ) 
					 + sin( radians(" . $_POST["lat_orig"] . ") ) * sin( radians( lat ) ) ) ) AS distance FROM `$table` 
					 HAVING `distance` < " . $_POST["distance"]/1000 . ") AS nearby";
}

// if looking for toilets with certain amenities
// select toilet from amenities where type = any type requested. if returned rows do not have all types requested, it fails.
if (isset($_POST["amenities"])) {
	$amenities = array();
	$amenities = explode(',',$_POST["amenities"]);
	$query = $query . ", (SELECT `toilet` FROM (SELECT `toilet`,COUNT(`toilet`) as counts FROM `amenities` WHERE";
	foreach ($amenities as $amenity) {
		// insert AND if necessary
		if ($or)
			$query = $query . " OR";
		$query = $query . " `type`='$amenity'";
		$or = true;
	}
	$query = $query . " GROUP BY `toilet`) AS amenable WHERE `counts` >= " . count($amenities) . ") AS ameniful";
}

// add where
$query = $query . " WHERE";

// if looking for toilets within a certain distance
if (isset($_POST["distance"]) && isset($_POST["lat_orig"]) && isset($_POST["lng_orig"])) {
	if ($and)
		$query = $query . " AND";
	$query = $query . " `nearby`.`id` = `$table`.`id`";
	$and = true;
}

if (isset($_POST["amenities"])) {
	if ($and)
		$query = $query . " AND";
	$query = $query . " `ameniful`.`toilet` = `$table`.`id`";
	$and = true;
}

// build predicates from post params
foreach ($params as $param)
	add_predicate($param);

// if no post params, then select everything
if (!$and)
	$query = "SELECT * FROM `$table`";

// terminate query with semicolon
$query = $query . ';';

// display query
if (isset($_POST["debug"]) && $_POST["debug"])
	echo $query . "<br>";

// import database connection variables
require_once __DIR__ . '/../utils/db_connect.php';

// connect to db
$db = db_connect();

// execute query
if ($result = $db->query($query)) {
	$response["success"] = 1; // success
	$response["message"] = $db->affected_rows . " toilet(s) found";

	// add results to response
	$result->data_seek(0);
	$response["$table"] = array();
	while ($row = $result->fetch_assoc()) {
		$toilet = array();
		foreach ($params as $param)
			if (isset($row[$param]))
				$toilet[$param] = $row[$param];

		array_push($response["$table"], $toilet);
	}
} else {
	$response["success"] = 0; // fail
	$response["message"] = $db->error . '(' . $db->errno . ')';
}

// TODO: make this part of a destructor function on a db class
$db->close();

// response
echo json_encode($response);

function add_predicate($param) {
	global $query, $and;
	if (isset($_POST[$param])) {
		// insert AND if necessary
		if ($and)
			$query = $query . " AND";
		$query = $query . " `". $param . "`='" . $_POST[$param] . "'";
		$and = true;
	}
}
?>